# 导入:
# import mysql.connector.errorcode
import sqlalchemy
from sqlalchemy import Column, String, Integer, DateTime, and_, func, exists, or_
from utils.sql.db import session, Base
from utils.sql.app.feedback import Feedback
import time


# 定义User对象:
class Message(Base):
    # 表的名字:
    __tablename__ = 'message'

    # 表的结构:
    id = Column(Integer(), primary_key=True, default=None)
    msg_title = Column(String(20), default='')
    msg_cont = Column(String(255), default='')
    start_time = Column(String(255), default='')
    end_time = Column(String(255), default='')
    msg_recipient = Column(String(255), default='')
    feedback_id = Column(String(255), default='')
    create_time = Column(String(20), default=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))


# 发布消息
def publish(data):
    # print(data)
    # return
    try:
        # 创建新Message对象:
        new_message = Message(
            msg_title=data.msg_title,
            msg_cont=data.msg_cont,
            start_time=data.start_time,
            end_time=data.end_time,
            msg_recipient=data.msg_recipient,
            feedback_id=data.id,
            create_time=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        )
        if data.id:
            feedbacks = session.query(Feedback).filter(Feedback.user_id == data.msg_recipient,
                                                       Feedback.id == data.id).one()
            feedbacks.dispose_state = True
        # 添加到session:
        session.add(new_message)
        # 提交即保存到数据库:
        session.commit()
        return {
            "code": 200,
            "data": "",
            "message": "提交成功"
        }
    except sqlalchemy.exc.IntegrityError as e:
        return {
            "code": 201,
            "data": e,
            "message": "提交失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 获取消息
def messages(user_id):
    # record = None
    try:
        current_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
        # session.query(Post).fitler(extract('month', Post.created) == 7).all()
        # .order_by(Message.id.desc())
        lists = session.query(Message).filter(
            and_(
                current_time >= Message.start_time,
                current_time <= Message.end_time
            ),
            or_(
                Message.msg_recipient == '0',
                Message.msg_recipient == user_id
            )
        ).all()
        return {
            "code": 200,
            "data": lists,
            "message": '查询成功'
        }
    except sqlalchemy.exc.NoResultFound as e:
        return {
            "code": 201,
            "data": e,
            "message": '查询失败'
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 查询消息列表
def get_messagelist(query: str, pageNum=1, pageSize=10):
    try:
        lists = session.query(Message).order_by(Message.create_time.desc())
        start = (int(pageNum) - 1) * int(pageSize)
        if query:
            lists = lists.filter(Message.msg_title.like(f'%{query}%'))
        msg = lists.limit(pageSize).offset(start).all()
        return {
            "code": 200,
            "data": {
                "list": msg,
                "current": pageNum,
                "total": lists.count()
            },
            "message": '查询成功'
        }
    except sqlalchemy.exc.NoResultFound as e:
        return {
            "code": 201,
            "data": e,
            "message": '查询失败'
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 删除消息列表
def delete_message(data):
    try:
        message = session.query(Message).filter(Message.id == data.id)
        # 执行删除操作
        message.delete()
        # 提交事务
        session.commit()
        return {
            "code": 200,
            "data": '',
            "message": "操作成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()
